﻿CREATE FUNCTION [dbo].[SplitOnPairsStringByDelimiter](
	@text nvarchar(max),
	@delimiter nvarchar(8) = '###',
	@pairDelimiter nvarchar(8) = '##'
)
RETURNS @Strings TABLE
(
[key] nvarchar(max) NOT NULL,
[value] nvarchar(max)
)
AS
BEGIN
	DECLARE @INDEX int
  SET @INDEX = -1
  
	WHILE (LEN(@text) > 0)
	BEGIN
		DECLARE @key nvarchar(max) = NULL, @value nvarchar(max) = NULL
		DECLARE @t table(	
			id int PRIMARY KEY,
			value nvarchar(max)
		)

		DELETE FROM @t
		
		SET @INDEX = CHARINDEX(@delimiter , @text)
		
		IF (@INDEX = 0) AND (LEN(@text) > 0)
		BEGIN
			INSERT INTO @t (id, value)
			SELECT id, value FROM [dbo].[SplitStringByDelimiter] (@text, @pairDelimiter)	
				
			SELECT @key = value FROM @t WHERE id = 1
			SELECT @value = value FROM @t WHERE id = 2
				
			INSERT INTO @Strings VALUES (@key, @value)
			BREAK
		END

		IF (@INDEX > 1)
		BEGIN
			DECLARE @tempText nvarchar(max) = LEFT(@text, @INDEX - 1)
			
			INSERT INTO @t (id, value)
			SELECT id, value FROM [dbo].[SplitStringByDelimiter] (@tempText, @pairDelimiter)	
				
			SELECT @key = value FROM @t WHERE id = 1
			SELECT @value = value FROM @t WHERE id = 2
				
			INSERT INTO @Strings VALUES (@key, @value)
		
			--INSERT INTO @Strings VALUES (LEFT(@text, @INDEX - 1))
			SET @text = RIGHT(@text, (LEN(@text) - (@INDEX - 1 + LEN(@delimiter))))
		END
		ELSE
		BEGIN
			 SET @text = RIGHT(@text, (LEN(@text) - (@INDEX - 1 + LEN(@delimiter))))
		END

	END
	
  RETURN
END